﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;
using System.Security.Cryptography;
using System.Text.RegularExpressions;
using System.Xml;
using System.Xml.XPath;
using System.Net.Configuration; 
using System.Web.Security; 
using System.Web.SessionState; 

namespace APIB
{ 
    public class ifsb : IHttpHandler 
    {
        apibmember m = new apibmember(); 
        public string getConn()
        {
              return ConfigurationManager.ConnectionStrings["IFSModel.Properties.Settings.IFN_dbConnectionString"].ConnectionString.ToString(); 
            //return "Data Source=27.254.67.212;uid=IFN-db;pwd=Ifuns@ccerdb2013;Initial Catalog=IFN-db"; 

        }

        public string GetJson(DataTable dt)
        {

            System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
            List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
            Dictionary<string, object> row = default(Dictionary<string, object>);

            foreach (DataRow dr in dt.Rows)
            {
                row = new Dictionary<string, object>();
                foreach (DataColumn col in dt.Columns)
                {
                    row.Add(col.ColumnName, dr[col]);
                }
                rows.Add(row);
            }

            return serializer.Serialize(rows);
        }

        private DataTable getdata()
        {
            SqlConnection Conn = new SqlConnection(getConn());
            SqlDataAdapter adapter = new SqlDataAdapter();


            string query = "select  * from members";

            adapter.SelectCommand = new SqlCommand(query, Conn);

            DataTable dt = new DataTable();

            Conn.Open();
            try
            {
                adapter.Fill(dt);
            }
            catch (Exception ex)
            {
                return dt = null;
            }

            finally
            {
                Conn.Close();
            }

            return dt;

        }

        public void ProcessRequest(HttpContext context)
        {

            SqlConnection Conn = new SqlConnection(getConn());

            context.Response.ContentType = "application/json";
            System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
             
            var MethodName = context.Request.Params["method"];
            var viewby = context.Request.Params["viewby"];
            var leagueSelected = context.Request.Params["leagueSelected"];

            DataTable dt = new DataTable();
            SqlDataAdapter adapter = new SqlDataAdapter();
            string query = "";

            switch (MethodName)
            {
                case "getdataallmember":   
                   var d = m.GetallMember();
                    context.Response.ContentType = "text/plain";
                    context.Response.Write(d);
                    context.Response.End();

                 break;

                case "getdataviewby":

                 var pa = context.Request.Params["leagueid"];

                    //dt = m.getdataviewby(pa); 
                    if (dt.Rows.Count > 0)
                    {
                        context.Response.Write(GetJson(dt));
                        return;
                    }

                 break;


                case "getLeague": 

                 query = @"select * from dbo.league where type = 1"; 

                 adapter.SelectCommand = new SqlCommand(query, Conn);
                     
                 dt = new DataTable();

                 Conn.Open();
                 try
                 {
                     adapter.Fill(dt);
                 }
                 catch
                 {
                     Conn.Close();
                 }
                 finally
                 {
                     Conn.Close();
                 }


                 if (dt.Rows.Count > 0)
                 {
                     context.Response.Write(GetJson(dt));
                     return;
                 }

                 break;


                case "getrankingall": 
                     

                     if (viewby == "monthscore")
                     {
                        //query = @"select a.memid,b.FirstName,SUM(a.Mpt_Cal_Point) curent_point, SUM(a.Mpt_Cal_Star) curent_star,
                        //       RankpointTotal = RANK() OVER (ORDER BY SUM(a.Mpt_Cal_Point) DESC)  
                        //       from Members_point_tran a inner join members b on a.memid = b.memid
                        //       where  a.typeid = 3 and datepart(month,a.createdate) = datepart(month,getdate()) group by a.memid,b.FirstName";

                         if (leagueSelected == "0")
                         {
                             query = @"select a.memid,b.FirstName,SUM(a.Mpt_Cal_Point) curent_point, SUM(a.Mpt_Cal_Star) curent_star,
                                        RankpointTotal = RANK() OVER (ORDER BY SUM(a.Mpt_Cal_Point) DESC)  
                                        from Members_point_tran a inner join members b on a.memid = b.memid inner join Members_play c on a.RefID = c.MempID
                                        where a.typeid = 3 and datepart(month,a.createdate) = datepart(month,getdate()) 
                                        group by a.memid,b.FirstName";
                         }
                         else {

                             query = @"select a.memid,b.FirstName,SUM(a.Mpt_Cal_Point) curent_point, SUM(a.Mpt_Cal_Star) curent_star,
                                        RankpointTotal = RANK() OVER (ORDER BY SUM(a.Mpt_Cal_Point) DESC)  
                                        from Members_point_tran a inner join members b on a.memid = b.memid inner join Members_play c on a.RefID = c.MempID
                                        where c.LeagueID = @LeagueID and  a.typeid = 3 and datepart(month,a.createdate) = datepart(month,getdate()) 
                                        group by a.memid,b.FirstName ";
                         }
                         
                     }
                     else // view by total lessson
                     {
                         if (leagueSelected == "0")
                         {
                             query = @"SELECT memid,FirstName, curent_point,curent_star, RANK() OVER (ORDER BY curent_point DESC) AS RankpointTotal,
                                    RANK() OVER (ORDER BY curent_star DESC) AS RankstarTotal
                                    FROM (select memid,curent_point,curent_star,FirstName from members ) as a order by RankpointTotal,RankstarTotal";
                         }
                         else
                         {
                             query = @"select a.memid,b.FirstName,SUM(a.Mpt_Cal_Point) curent_point, SUM(a.Mpt_Cal_Star) curent_star,
                                        RankpointTotal = RANK() OVER (ORDER BY SUM(a.Mpt_Cal_Point) DESC)  
                                        from Members_point_tran a inner join members b on a.memid = b.memid inner join Members_play c on a.RefID = c.MempID
                                        where c.LeagueID = @LeagueID and  a.typeid = 3  
                                        group by a.memid,b.FirstName ";

                         }
                     }
                      


                    adapter.SelectCommand = new SqlCommand(query, Conn);

                    if (leagueSelected != "0")
                    {
                        adapter.SelectCommand.Parameters.AddWithValue("@LeagueID", leagueSelected); 
                    }


                    dt = new DataTable();

                    Conn.Open();
                    try
                    {
                        adapter.Fill(dt);
                    }
                    catch
                    {
                        Conn.Close(); 
                    }
                    finally
                    {
                        Conn.Close();
                    }
                     

                    if (dt.Rows.Count > 0)
                    {
                        context.Response.Write(GetJson(dt));
                        return;
                    }

                 break;

            }

        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }






    }
}